package com.sq.partner.manager.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

import com.sq.partner.manager.dao.ITradCreditLmtTraceDao;
import com.sq.partner.manager.mybatis.model.DeptDto;
import com.sq.partner.manager.mybatis.model.NocarPlan;
import com.sq.partner.manager.mybatis.model.TradCreditLmtTrace;
import com.sq.partner.manager.util.DBUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.SqlUtil;
import com.sq.partner.manager.util.StrUtil;


@Repository
public class TradCreditLmtTraceDaoImpl extends BaseDaoImpl implements
		ITradCreditLmtTraceDao {
	private static Logger logger = LogUtil.getLog(TradCreditLmtTraceDaoImpl.class);

	@Override
	public void saveAppendMoney(TradCreditLmtTrace creditLmtTrace)
			throws Exception {

		Connection conn = null;
		PreparedStatement pstmt = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		
		//查询出t_trad_credit_lmt_trace表中的最大serial_no后，+1 ，拼成serial_no字段格式的字符串
		String sql = "select to_char(sysdate,'yyyy')||lpad(s_gateway_credit_trace.nextval,20,'0') from dual";
		try{
			conn = this.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				String serialNo = rs.getString(1);
				creditLmtTrace.setSerialNo(serialNo.toString());
			}
			//查询出此时相应的creditLmtDef对应的剩余保费
			String sql2 = "select nvl(b.save_money,0) from "+
							      " (select nvl(max(serial_no),0) max_serial_no from t_trad_credit_lmt_trace group by limit_no) a,"+
							      " t_trad_credit_lmt_trace b"+
							 " where a.max_serial_no = b.serial_no and b.limit_no = ?";
			pstmt1 = conn.prepareStatement(sql2);
			pstmt1.setString(1, creditLmtTrace.getLimitNo());
			
			rs = pstmt1.executeQuery();
			
			if(rs.next()){
				creditLmtTrace.setSaveMoney(rs.getBigDecimal(1).add(creditLmtTrace.getAppendMoney()));
			}else{
				creditLmtTrace.setSaveMoney(creditLmtTrace.getAppendMoney());
			}
			
			this.add("insertTradCreditLmtTrace", creditLmtTrace);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
	}

	@Override
	public Page queryAppendDetailInfoByLimitNo(Integer pageIndex, TradCreditLmtTrace creditLmtTrace)
			throws Exception {
		Connection conn = null;
		//查总条数
		PreparedStatement pstmt = null;
		//查分页
		PreparedStatement pstmt1 = null;
		ResultSet rs = null;
		Page page = null;
		try{
			conn = this.getConnection();
			StringBuffer sqlbuf=new StringBuffer();
			sqlbuf.append("SELECT M.SUB_CHANNEL_NAME as SUB_CHANNEL_NAME, T.APPEND_MONEY, TO_CHAR(T.CREAT_TIME, 'yyyy-MM-dd HH24:MI:SS') AS CREATE_TIME, T.REMARK_DSCP"
				  +" FROM T_TRAD_CREDIT_LMT_TRACE T " 
				 +" INNER JOIN T_GATEWAY_SUB_CHANNEL M "
				    +" ON T.PARTNER_CODE = M.SUB_CHANNEL_CODE "
				 +" WHERE T.LIMIT_NO = '"+creditLmtTrace.getLimitNo()+"' "
				   +" AND T.APPEND_TYPE = '03'");
			List list=new ArrayList();
			List<Boolean> islike=new ArrayList<Boolean>();
 			if(StrUtil.isNotEmpty(creditLmtTrace.getBeginTime())){
				sqlbuf.append(" AND trunc(t.creat_time)  > to_date(?,'yyyy-MM-dd')-1");
				list.add(creditLmtTrace.getBeginTime());
				islike.add(false);
			}
			if(StrUtil.isNotEmpty(creditLmtTrace.getEndTime())){
				sqlbuf.append(" AND trunc(t.creat_time)  < to_date(?,'yyyy-MM-dd')+1");
				list.add(creditLmtTrace.getEndTime());
				islike.add(false);
			}
			if(null != creditLmtTrace.getAppendBeginMoney()){
				sqlbuf.append(" AND t.append_money >= ?");
				list.add(creditLmtTrace.getAppendBeginMoney());
				islike.add(false);
			}
			if(null != creditLmtTrace.getAppendEndMoney()){
				sqlbuf.append(" AND t.append_money <= ?");
				list.add(creditLmtTrace.getAppendEndMoney());
				islike.add(false);
			}
			sqlbuf.append(" ORDER BY T.CREAT_TIME DESC ");
			logger.debug("执行SQL：" + sqlbuf.toString());
			pstmt = conn.prepareStatement("select count(*) from ("+sqlbuf.toString()+")");
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt.setObject(i+1, list.get(i));
				}
				
			}
			rs = pstmt.executeQuery();
			rs.next();
			Long total=0l;
			total= rs.getLong(1);
			page = new Page(pageIndex,total);
			//查出分页
			SqlUtil sqlUtil=new	SqlUtil();
			String pagesql=sqlUtil.buildPageSqlForOracle(sqlbuf.toString(),page).toString();
			logger.debug("执行SQL："+pagesql);
			pstmt1 = conn.prepareStatement(pagesql);
			for (int i = 0; i < list.size(); i++) {
				if(islike.get(i)){
					pstmt1.setObject(i+1, "%"+list.get(i)+"%");
				}else{
					pstmt1.setObject(i+1, list.get(i));
				}
			}
			rs = pstmt1.executeQuery();
			List lists = DBUtil.transRs2List(rs);
			page.setData(lists);
		}finally{
			close(rs);
			close(pstmt);
			close(pstmt1);
			close(conn);
		}
		return page;
	
	}

}
